package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.DBTool;
import model.Page;
import model.User;
import model.Weeklog;
import model.WeeklogMessage;
import DAO.WeeklogDAO;

public class WeeklogImple implements WeeklogDAO {


	public int addWeeklog(Weeklog weekLog) {
		int result = 0;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		String sql = "insert into T_WeekLog"+
				"(userId,publishTime,schoolYear,semester,weekNum,teammateExchange,fridayExchange,teammates,studyContent,questions,groupID)"+
				"values(?,?,?,?,?,?,?,?,?,?,?)";
		pstmt = dbt.getPstmt(sql);
		try {
			pstmt.setInt(1, weekLog.getUser().getId());
			pstmt.setString(2, (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())));
			pstmt.setInt(3, weekLog.getSchoolYear());
			pstmt.setInt(4, weekLog.getSemester());
			pstmt.setInt(5, weekLog.getWeekNum());
			pstmt.setString(6, weekLog.getTeammateExchange());
			pstmt.setString(7, weekLog.getFridayExchange());
			pstmt.setString(8, weekLog.getTeammates());
			pstmt.setString(9, weekLog.getStudyContent());
			pstmt.setString(10, weekLog.getQuestions());
			pstmt.setInt(11, weekLog.getGroup().getId());
			
			int urs= pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return result;
	}

	public void deleteWeeklog(int id) {
		
		String sql = "DELETE FROM T_WeekLog WHERE id = ?";
		
		Weeklog weeklog = new Weeklog();
		PreparedStatement pstmt = null;
		
		DBTool dbt = DBTool.getDBTool();
		
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, weeklog.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
	}
	
	public List<Weeklog> getWeeklogByUser(User user, Page page) {
		
		String sql = "SELECT w.*, u.name FROM T_WeekLog as w, T_User as u WHERE w.userId = u.id and u.name = ? ORDER BY w.id DESC LIMIT ?, ?" ;
		
		Weeklog weeklog = null;
		List<Weeklog> weeklogList = new ArrayList<Weeklog>();
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		try {
			pstmt.setString(1, user.getName());
			pstmt.setInt(2, page.getStartRow());
			pstmt.setInt(3, page.getEndRow());
			rst = pstmt.executeQuery();
			while(rst.next()) {
				weeklog = new Weeklog();
				weeklog.setId(rst.getInt("id"));
				weeklog.setSchoolYear(rst.getInt("w.schoolYear"));
				weeklog.setSemester(rst.getInt("w.semester"));
				weeklog.setWeekNum(rst.getInt("w.weekNum"));
				weeklog.setPublishTime(rst.getDate("w.publishTime"));
//			    weeklog.setGroupId(rst.getInt("groupID"));
				weeklog.setTeammateExchange(rst.getString("w.teammateExchange"));
				weeklog.setFridayExchange(rst.getString("w.fridayExchange"));
				weeklog.setTeammates(rst.getString("w.teammates"));
				weeklog.setStudyContent(rst.getString("studyContent"));
				weeklog.setQuestions(rst.getString("w.questions"));
				
				weeklogList.add(weeklog);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return weeklogList;
	}
	
	public Weeklog getWeeklogById(int id) {

		String sql = "SELECT w.*, u.* FROM T_WeekLog as w, T_User as u WHERE w.userId = u.id AND w.id = ?";
	
		Weeklog weeklog = new Weeklog();
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		
		try {
			
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			rst = pstmt.executeQuery();
			if(rst.next()) {
				weeklog.setId(rst.getInt("w.id"));
				weeklog.setUser(new User());
				weeklog.getUser().setName(rst.getString("u.name"));
				weeklog.getUser().setId(rst.getInt("w.userId"));
				weeklog.setSchoolYear(rst.getInt("w.schoolYear"));
				weeklog.setSemester(rst.getInt("w.semester"));
				weeklog.setWeekNum(rst.getInt("w.weekNum"));
				weeklog.setPublishTime(rst.getTimestamp("w.publishTime"));
				weeklog.setTeammateExchange(rst.getString("w.teammateExchange"));
				weeklog.setFridayExchange(rst.getString("w.fridayExchange"));
				weeklog.setTeammates(rst.getString("w.teammates"));
				weeklog.setStudyContent(rst.getString("w.studyContent"));
				weeklog.setQuestions(rst.getString("w.questions"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return weeklog;
	}

	public List<Weeklog> getWeeklogList(int userId, Page page) {
		
		//String sql = "SELECT w.*, g.groupName, g.id FROM T_Weeklog as w, T_User as u, T_Group as g WHERE w.userId = u.id and u.groupID = g.id and w.state = 0 order by w.id limit ?, ?";
		
		List<Weeklog> weeklogList = new ArrayList<Weeklog>();
		Weeklog weekLog = null;
		User user = null;
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		
		String sql = "select w.*, u.* from T_WeekLog as w, T_User as u where w.userId = u.id AND w.userId = ? ORDER BY w.id DESC LIMIT ?, ?";
		pstmt = dbt.getPstmt(sql);
		try {
			pstmt.setInt(1, userId);
			pstmt.setInt(2, page.getStartRow());
			pstmt.setInt(3, page.getPageSize());
			rst = pstmt.executeQuery();
			while(rst.next()) {
				weekLog = new Weeklog();
				weekLog.setUser(new User());
				weekLog.getUser().setId(rst.getInt("w.userId"));
//				weekLog.getUser().setName(rst.getString("u.name"));
				weekLog.setId(rst.getInt("w.id"));
				weekLog.setUserId(rst.getInt("w.userId"));
				weekLog.setSchoolYear(rst.getInt("w.schoolYear"));
				weekLog.setSemester(rst.getInt("w.semester"));
				weekLog.setWeekNum(rst.getInt("w.weekNum"));
				weekLog.setStudyContent(rst.getString("w.studyContent"));
				weekLog.setPublishTime(rst.getTimestamp("w.publishTime"));
				weeklogList.add(weekLog);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return weeklogList;
	}

	public void updateWeeklog(int id) {
		
		String sql = "UPDATE T_WeekLog SET teammateExchange = ?, fridayExchange = ?, studyContent = ?, questions = ?";
		
		PreparedStatement pstmt = null;
		
		Weeklog weekLog = getWeeklogById(id);
		
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setString(1, weekLog.getTeammateExchange());
			pstmt.setString(2, weekLog.getFridayExchange());
			pstmt.setString(3, weekLog.getStudyContent());
			pstmt.setString(4, weekLog.getQuestions());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
	}
	
	public int addWeeklogMessage(WeeklogMessage weeklogMessage) {
		int result = 0;
		String sql = "insert into T_WeeklogMessage (messager, messageFor, publishTime, content) values(?, ?, ?, ?)";
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, weeklogMessage.getMessager().getId());
			pstmt.setInt(2, weeklogMessage.getMessageFor().getId());
			pstmt.setString(3, (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())));
			pstmt.setString(4, weeklogMessage.getContent());
			
			int rs = pstmt.executeUpdate();
			
			if(rs != 0) {
				result = 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<WeeklogMessage> getWeeklogMessage(int id, Page page) {
		
		String sql = "SELECT w.*, wm.*, u1.id, u2.id, u1.name, u2.name FROM T_WeekLog as w, T_WeeklogMessage as wm, T_User as u1, T_User as u2 WHERE wm.messager = u1.id AND wm.messageFor = u2.id AND w.id = ? ORDER BY wm.id DESC LIMIT ?, ?";
		
		Weeklog weekLog = null;
		WeeklogMessage weeklogMessage = new WeeklogMessage();;
		List<WeeklogMessage> weeklogMessageList = new ArrayList<WeeklogMessage>();
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		DBTool dbt = DBTool.getDBTool();
		
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, id);
			pstmt.setInt(2, page.getStartRow());
			pstmt.setInt(3, page.getEndRow());
			rst = pstmt.executeQuery();
			while(rst.next()) {
				weekLog = new Weeklog();
				weekLog.setId(rst.getInt("w.id"));
				weeklogMessage = new WeeklogMessage();
				weeklogMessage.setId(rst.getInt("wm.id"));
				weeklogMessage.setMessager(new User());
				weeklogMessage.getMessager().setId(rst.getInt("u1.id"));
				weeklogMessage.getMessager().setName(rst.getString("u1.name"));
				weeklogMessage.setMessageFor(new User());
				weeklogMessage.getMessageFor().setId(rst.getInt("u2.id"));
				weeklogMessage.getMessageFor().setName(rst.getString("u2.name"));
				weeklogMessage.setContent(rst.getString("content"));
				weeklogMessage.setPublishTime(rst.getDate("publishTime"));
				
				weeklogMessageList.add(weeklogMessage);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return weeklogMessageList;
	}

	public void deleteWeeklogMessage(int id) {
		
		String sql = "DELETE FROM T_WeeklogMessage WHERE id = ?";
		
		WeeklogMessage weeklogMessage = new WeeklogMessage();
		
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			pstmt.setInt(1, weeklogMessage.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
	}
	
}


